const express = require('express');
const router = express.Router();
const sql = require('../db.js');

/**
 * 用户注册api
 * 传入sql语句，向账户表插入一条新记录
 * [] 里的 req.body.uid 等参数(都是前端传过来的)分别代表 sql 语句里的问号
 */
router.post('/register', (req, res) => {
  if (req.body) {
    sql('insert into users_accounts (uid, email, pwd) values (?, ?, ?)', 
      [req.body.uid, req.body.email, req.body.password])
      .then((result) => {
        if (result.affectedRows > 0) {
          res.json({ code: 200, data: '新增成功' })
        } else {
          res.json({ code: 400, data: '新增失败' })
        }
      })
      .catch(() => {
        res.json({ code: 500, data: '程序错误' })
      })
  }
})

/**
 * 用户登录
 * 传入邮箱和密码到数据库里验证是否匹配
 * 如果匹配，则登录成功，否则失败
 */
router.post('/sign', (req, res) => {
  if (req.body) {
    sql('select * from users_accounts where email=? and pwd=?',
      [req.body.email, req.body.password])
      .then((result) => {
        res.json({ code: 200, data: result })
      })
      .catch(() => {
        res.json({ code: 500, data: '程序错误' })
      })
  }
})

/**
 * 发布帖子
 */
router.post('/commun', (req, res) => {
  if (req.body) {
    sql('insert into communication (name,email,content,createTime) values (?,?,?,?)',
      [req.body.name, req.body.email, req.body.content, req.body.createTime])
      .then((result) => {
        if (result.affectedRows > 0) {
          res.json({ code: 200, data: '发布成功' })
        } else {
          res.json({ code: 400, data: '发布失败' })
        }
      })
      .catch(() => {
        res.json({ code: 500, data: '程序错误' })
      })
  }
})

/**
 * 获取帖子
 */
router.get('/getCommun', (req, res) => {
  sql('select * from communication')
    .then((result) => {
      res.json({ code: 200, data: result})
    })
    .catch((err) => {
      res.json({ code: 500, data: err })
    })
})

/**
 * 删除帖子
 */
router.delete('/delCommun/:id', (req, res) => {
  if (req.params.id) {
    sql('delete from communication where id=?', req.params.id)
      .then((result) => {
        if (result.affectedRows > 0) {
          res.json({ code: 200, data: '删除成功' })
        } else {
          res.json({ code: 400, data: '删除失败' })
        }
      })
      .catch((err) => {
        res.json({ code: 500, data: err })
      })
  }
})

/**
 * 回复帖子
 */
router.post('/apply', (req, res) => {
  if (req.body) {
    sql('insert into apply_msg (id,name,content,createTime) values (?,?,?,?)',
      [req.body.id, req.body.name, req.body.content, req.body.createTime])
      .then((result) => {
        if (result.affectedRows > 0) {
          res.json({ code: 200, data: '回复成功' })
        } else {
          res.json({ code: 400, data: '回复失败' })
        }
      })
      .catch((err) => {
        res.json({ code: 500, data: err })
      })
  }
})

/**
 * 获取所有帖子回复消息
 */
router.get('/getApplyMsg', (req, res) => {
  sql('select * from apply_msg')
    .then((result) => {
      res.json({ code: 200, data: result})
    })
    .catch((err) => {
      res.json({ code: 500, data: err })
    })
})

/**
 * 游戏模块获取问题
 */
router.get('/getQuestion', (req, res) => {
  sql('select * from game')
    .then((result) => {
      res.json({ code: 200, data: result })
    })
    .catch((err) => {
      res.json({ code: 500, data: err })
    })
})

/**
 * 记录用户答题分数
 */
router.post('/addTop', (req, res) => {
  if (req.body) {
    sql('insert into game_top (name,score,createTime) values (?,?,?)',
    [req.body.name, req.body.score, req.body.createTime])
      .then((result) => {
        if (result.affectedRows > 0) {
          res.json({ code: 200, data: '记录成功' })
        } else {
          res.json({ code: 400, data: '记录失败' })
        }
      })
      .catch((err) => {
        res.json({ code: 500, data: err })
      })
  }
})

/**
 * 游戏模块获取排行榜
 * 去重排序
 */
router.get('/getTop', (req, res) => {
  sql('SELECT * from game_top WHERE score in (SELECT max(score) from game_top GROUP BY name) ORDER BY score DESC')
    .then((result) => {
      res.json({ code: 200, data: result })
    })
    .catch((err) => {
      res.json({ code: 500, data: err })
    })
})

/**
 * 获取用户的答题记录
 */
router.get('/getMyRecord/:id', (req, res) => {
  sql('SELECT * from game_top where name=?', [req.params.id])
    .then((result) => {
      res.json({ code: 200, data: result})
    })
    .catch((err) => {
      res.json({ code: 500, data: err})
    })
})

module.exports = router;
